﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for _Article
/// </summary>
public class _Article
{
    public _Article()
    {
        //
        // TODO: Add constructor logic here
        //
    }

    public int _id { get; set; }
    public int _id_document { get; set; }
    public string _article_name { get; set; }
    public int _article_count { get; set; }
    public decimal _article_price { get; set; }
    public decimal _article_sum { get; set; }

    static SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);

    private static _Article FillArticle(SqlDataReader dr)
    {
        _Article article = new _Article();
        article._id = (int)dr[0];
        article._id_document = (int)dr[1];
        article._article_name = dr[2].ToString();
        article._article_count = (int)dr[3];
        article._article_price = (decimal)dr[4];
        article._article_sum = (decimal)dr[5];
        return article;
    }

    public static List<_Article> SlectArticle(string document_id)
    {
        List<_Article> article = new List<_Article>();
        List<int> article_id = new List<int>();
        SqlCommand SelCmd_id = new SqlCommand("SELECT id FROM article WHERE id_document=@document_id", conn);
        SelCmd_id.Parameters.Add(new SqlParameter("@document_id", System.Data.SqlDbType.Int)).Value = document_id;
        conn.Open();
        SqlDataReader dr = SelCmd_id.ExecuteReader();
        while (dr.Read())
        {
            article_id.Add(Convert.ToInt32(dr["id"]));
        }
        dr.Close();
        conn.Close();
        foreach (int art in article_id)
        {
            conn.Close();
            SqlCommand SelCmd = new SqlCommand("SELECT * FROM article WHERE id=@id", conn);
            SelCmd.Parameters.Add(new SqlParameter("@id", System.Data.SqlDbType.Int)).Value = art;
            conn.Open();
            SqlDataReader dr1 = SelCmd.ExecuteReader();
            while (dr1.Read())
            {
                article.Add(FillArticle(dr1));
            }
            dr.Close();
            conn.Close();
        }
        return article;
    }

    //Article pievienšana AddDocument.aspx
    public static _Article Insert(string id_docu, string art_name, string art_count, string art_price)
    {
        _Article article = new _Article();
        SqlCommand AddCmd = new SqlCommand("INSERT INTO article (id_document, article_name, article_count, article_price)" + "VALUES(@id_document, @article_name, @article_count, @article_price);", conn);
        AddCmd.Parameters.Add(new SqlParameter("@id_document", System.Data.SqlDbType.Int)).Value = id_docu;
        AddCmd.Parameters.Add(new SqlParameter("@article_name", System.Data.SqlDbType.NVarChar)).Value = art_name;
        AddCmd.Parameters.Add(new SqlParameter("@article_count", System.Data.SqlDbType.Int)).Value = art_count;
        AddCmd.Parameters.Add(new SqlParameter("@article_price", System.Data.SqlDbType.Decimal)).Value = art_price;
        conn.Open();
        AddCmd.ExecuteNonQuery();
        conn.Close();
        return article;
    }

    //Article labošana EditDocunemt.aspx
    public static _Article Update(string id_art, string id_docu, string art_name, string art_count, string art_price)
    {
        _Article article = new _Article();
        SqlCommand UpCmd = new SqlCommand("UPDATE article SET id_document=@id_document, article_name=@article_name, article_count=@article_count, article_price=@article_price WHERE ID=@ID", conn);
        UpCmd.Parameters.Add(new SqlParameter("@ID", System.Data.SqlDbType.Int)).Value = id_art;
        UpCmd.Parameters.Add(new SqlParameter("@id_document", System.Data.SqlDbType.Int)).Value = id_docu;
        UpCmd.Parameters.Add(new SqlParameter("@article_name", System.Data.SqlDbType.NVarChar)).Value = art_name;
        UpCmd.Parameters.Add(new SqlParameter("@article_count", System.Data.SqlDbType.Int)).Value = art_count;
        UpCmd.Parameters.Add(new SqlParameter("@article_price", System.Data.SqlDbType.Decimal)).Value = art_price;
        conn.Open();
        UpCmd.ExecuteNonQuery();
        conn.Close();
        return article;
    }

    //Article dzēšana Default.aspx
    public static _Article Delete(int id_article)
    {
        _Article article = new _Article();
        SqlCommand DelCmd = new SqlCommand("DELETE FROM article WHERE (id = @id_article)", conn);
        DelCmd.Parameters.Add(new SqlParameter("@id_article", System.Data.SqlDbType.Int)).Value = id_article;
        conn.Open();
        DelCmd.ExecuteNonQuery();
        conn.Close();
        return article;
    }

    //Article dzēšana Default.aspx
    //public static _Article Delete_all_articles(string id_document)
    //{
    //    _Article article = new _Article();
    //    List<int> article_id = new List<int>();
    //    SqlCommand SelCmd = new SqlCommand("SELECT id FROM article WHERE id_document=@document_id", conn);
    //    SelCmd.Parameters.Add(new SqlParameter("@document_id", System.Data.SqlDbType.Int)).Value = id_document;
    //    conn.Open();
    //    SqlDataReader dr = SelCmd.ExecuteReader();
    //    while (dr.Read())
    //    {
    //        article_id.Add(Convert.ToInt32(dr["id"].ToString()));
    //   }
    //    dr.Close();
    //    conn.Close();
    //    foreach (int artic_id in article_id)
    //    {
    //        SqlCommand DelCmd = new SqlCommand("DELETE FROM article WHERE (id = @id_article)", conn);
    //        DelCmd.Parameters.Add(new SqlParameter("@id_article", System.Data.SqlDbType.Int)).Value = artic_id;
    //        conn.Open();
    //        DelCmd.ExecuteNonQuery();
    //        conn.Close();
    //    }
    //    return article;
    //}
}